from django import http
import pymysql
import json
import time


def list_pro(request):
    t = ''

    if 't' in request.GET and request.GET['t']:
        t = request.GET['t']

    db = db_connect()
    cursor = db.cursor()
    sql = "SELECT * FROM t_pro WHERE p_type='" + t + "'"
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return http.HttpResponse(json.dumps(data, ensure_ascii=False))


# 创建连接数据库
def db_connect():
    db = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
        db='mp'
    )
    return db


def addP(request):
    user_id = ""
    p_id = ""
    if 'user_id' in request.GET and request.GET['user_id']:
        user_id = request.GET['user_id']
    if 'p_id' in request.GET and request.GET['p_id']:
        p_id = request.GET['p_id']

    t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    null = '    '
    db = db_connect()
    cursor = db.cursor()
    sql = "INSERT INTO t_cart(user_id,p_id,added_time,order_num) VALUES(" + user_id + "," + p_id + ",'" + t + "','" + null + "')"
    # sql = "INSERT INTO t_cart_test(user_id,p_id,added_time) VALUES('"+user_id+"','"+p_id+"','"+t+"')"
    if cursor.execute(sql):
        cursor.close()
        db.close()
        return http.HttpResponse("yes")
    else:
        cursor.close()
        db.close()
        return http.HttpResponse("no")


# 购物车
def list_cart(request):
    user_id = ""
    if 'user_id' in request.GET and request.GET['user_id']:
        user_id = request.GET['user_id']

    db = db_connect()
    cursor = db.cursor()
    # sql = "SELECT t_cart.cart_id,t_pro.p_name,t_pro.p_size,t_pro.p_price,t_pro.p_pic FROM t_cart INNER JOIN t_pro ON t_cart.p_id=t_pro.p_id WHERE t_cart.user_id=" + user_id + "AND t_cart.order_num=''"

    sql = f"""
    SELECT t_cart.cart_id, t_pro.p_name, t_pro.p_size, t_pro.p_price, t_pro.p_pic
    FROM t_cart
    INNER JOIN t_pro ON t_cart.p_id=t_pro.p_id
    WHERE t_cart.user_id={user_id} AND t_cart.order_num = ''
    """
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return http.HttpResponse(json.dumps(data, ensure_ascii=False))


def subP(request):
    user_id = ""
    cart_id = ""
    if 'user_id' in request.GET and request.GET['user_id']:
        user_id = request.GET['user_id']
    if 'cart_id' in request.GET and request.GET['cart_id']:
        cart_id = request.GET['cart_id']

    db = db_connect()
    cursor = db.cursor()
    sql = f"DELETE FROM t_cart WHERE cart_id={cart_id}"

    if cursor.execute(sql):
        cursor.close()
        db.close()
        return http.HttpResponse("yes")
    else:
        cursor.close()
        db.close()
        return http.HttpResponse("no")


def countP(request):
    user_id = ""
    if 'user_id' in request.GET and request.GET['user_id']:
        user_id = request.GET['user_id']

    db = db_connect()
    cursor = db.cursor()
    sql = f"""
    SELECT COUNT(*),SUM(t_pro.p_price)
    FROM t_cart
    INNER JOIN t_pro ON t_cart.p_id=t_pro.p_id
    WHERE t_cart.user_id={user_id} AND t_cart.order_num = ''
    """

    cursor.execute(sql)
    data = cursor.fetchone()  # 直接获取单条数据

    cursor.close()
    db.close()

    count = str(data[0])
    total = data[1]
    if total:
        total = str(total)
    else:
        total = "0"

    inf = str(count + "," + total)
    return http.HttpResponse(inf)


def payP(request):
    user_id = ""
    if 'user_id' in request.GET and request.GET['user_id']:
        user_id = request.GET['user_id']

    db = db_connect()
    cursor = db.cursor()
    t = time.strftime("%Y%m%d%H%M%S", time.localtime())
    sql = f"UPDATE t_cart SET order_num={t} WHERE user_id={user_id}"
    if cursor.execute(sql):
        cursor.close()
        db.close()
        return http.HttpResponse("yes")
    else:
        cursor.close()
        db.close()
        return http.HttpResponse("no")
